﻿Imports System.Data.SqlClient
Public Class frmKetChuyen

    Dim TuThang As Integer = 0
    Dim TuNam As Integer = 0
    Dim DenThang As Integer = 0
    Dim DenNam As Integer = 0

    Private Sub frmKetChuyen_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If LayNgayKhoaSo() Then
            lblFrom.Text = TuThang & "/" & TuNam
            lblTo.Text = DenThang & "/" & DenNam
        End If
    End Sub

    Private Sub btnThucHien_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThucHien.Click
        If MsgBox("Khi thực hiện kết chuyển dữ liệu thì dữ liệu của tháng " & TuThang & "/" & TuNam & " sẽ không thể thay đổi." & vbNewLine & "Tiếp tục??", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            Me.btnThucHien.Enabled = False
            If KetChuyenDuLieu() Then
                TuThang = DenThang
                TuNam = DenNam
                ThangTiepTheo(DenThang, DenNam)
                lblFrom.Text = TuThang & "/" & TuNam
                lblTo.Text = DenThang & "/" & DenNam
                Me.btnThucHien.Enabled = True
                MsgBox("Kết chuyển thành công.")
            Else
                MsgBox("Lỗi kết chuyển!")
            End If
        End If
    End Sub

    Private Function LayNgayKhoaSo() As Boolean
        Dim result As Boolean = False
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim strSql As String = "select Top 1"
        strSql &= " Thang, Nam"
        strSql &= "    from KhoaSo "
        strSql &= " Order by convert(datetime,'01/' + cast(thang as varchar) + '/' + cast(nam as varchar),103) desc"
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.Fill(myTable)
            If myTable.Rows.Count > 0 Then
                TuThang = myTable.Rows(0)(0)
                TuNam = myTable.Rows(0)(1)
            End If
            ThangTiepTheo(TuThang, TuNam)
            DenThang = TuThang
            DenNam = TuNam
            ThangTiepTheo(DenThang, DenNam)

            result = True
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return result
    End Function

    Private Sub ThangTiepTheo(ByRef Thang As Integer, ByRef Nam As Integer)
        If Thang = 12 Then
            Thang = 1
            Nam = Nam + 1
        Else
            Thang = Thang + 1
        End If
    End Sub

    Private Function KetChuyenDuLieu() As Boolean
        Dim strSql As String = ""
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myComm As New SqlCommand
        Try
            strSql = "select MaHH from HangHoa"
            myConn.Open()
            myComm.CommandText = strSql
            myComm.Connection = myConn
            Dim myAdapter As New SqlDataAdapter(myComm)
            Dim myTable As New DataTable
            myAdapter.Fill(myTable)

            For Each row As DataRow In myTable.Rows
                strSql = "Select Top 1 "
                strSql &= " ct.MaPhieuCT, "
                strSql &= " sc.LoaiCT, "
                strSql &= " ct.SoLuong, "
                strSql &= " ct.TonKho, "
                strSql &= " ct.GiaVon, "
                strSql &= " ct.GiaTri, "
                strSql &= " case "
                strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
                strSql &= "	else ct.SoLuong * ct.DonGia "
                strSql &= " end as TienHang, "
                strSql &= " ct.ThueNK "
                strSql &= " from"
                strSql &= " CTSoCai ct"
                strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
                strSql &= " where "
                strSql &= " datepart(Month, sc.NgayCT) = @Thang "
                strSql &= " and datePart(Year, sc.NgayCT) = @Nam "
                strSql &= " and ct.MaHH = @MaHH "
                strSql &= " order by sc.NgayCT desc"
                myComm.CommandText = strSql
                myComm.Parameters.Clear()
                myComm.Parameters.AddWithValue("@MaHH", row(0))
                myComm.Parameters.AddWithValue("@Thang", TuThang)
                myComm.Parameters.AddWithValue("@Nam", TuNam)
                myAdapter = New SqlDataAdapter(myComm)
                Dim myTable2 As New DataTable
                myAdapter.Fill(myTable2)
                If myTable2.Rows.Count > 0 Then
                    Dim soluong As Double = myTable2.Rows(0)(3)
                    Dim giatri As Double = myTable2.Rows(0)(5)
                    Dim giavon As Double = 0
                    If myTable2.Rows(0)(1) = "PNK" Then
                        soluong = soluong + myTable2.Rows(0)(2)
                        giatri = giatri + myTable2.Rows(0)(6)
                        If myTable2.Rows(0)(7) IsNot DBNull.Value Then
                            giatri += myTable2.Rows(0)(7)
                        End If
                    Else
                        soluong = soluong - myTable2.Rows(0)(2)
                        giatri = giatri - (myTable2.Rows(0)(2) * myTable2.Rows(0)(4))
                    End If
                    If soluong <> 0 Then
                        giavon = giatri / soluong
                    End If
                    strSql = "Update CTHangHoa set SLTonCK = @1, GTTonCK = @2, GiaVonCK = @3, KhoaSo = 1 where MaHH = @4 and Thang = @5 and Nam = @6;"
                    strSql &= "Insert into CTHangHoa(MaHH, Thang, Nam, SLTonDK, GTTonDK, GiaVonDK, KhoaSo) values (@4, @7, @8, @1, @2, @3, 0);"
                    myComm.CommandText = strSql
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@1", soluong)
                    myComm.Parameters.AddWithValue("@2", giatri)
                    myComm.Parameters.AddWithValue("@3", giavon)
                    myComm.Parameters.AddWithValue("@4", row(0))
                    myComm.Parameters.AddWithValue("@5", TuThang)
                    myComm.Parameters.AddWithValue("@6", TuNam)
                    myComm.Parameters.AddWithValue("@7", DenThang)
                    myComm.Parameters.AddWithValue("@8", DenNam)
                    myComm.ExecuteNonQuery()
                Else
                    strSql = "Select SLTonDK, GTTonDK, GiaVonDK from CTHangHoa where MaHH = @MaHH and Thang = @Thang and Nam = @Nam"
                    myComm.CommandText = strSql
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@MaHH", row(0))
                    myComm.Parameters.AddWithValue("@Thang", TuThang)
                    myComm.Parameters.AddWithValue("@Nam", TuNam)
                    myAdapter = New SqlDataAdapter(myComm)
                    myTable2 = New DataTable
                    myAdapter.Fill(myTable2)
                    strSql = "Update CTHangHoa set SLTonCK = @1, GTTonCK = @2, GiaVonCK = @3, KhoaSo = 1 where MaHH = @4 and Thang = @5 and Nam = @6;"
                    strSql &= "Insert into CTHangHoa(MaHH, Thang, Nam, SLTonDK, GTTonDK, GiaVonDK, KhoaSo) values (@4, @7, @8, @1, @2, @3, 0);"
                    myComm.CommandText = strSql
                    myComm.Parameters.Clear()
                    If myTable2.Rows.Count > 0 Then
                        myComm.Parameters.AddWithValue("@1", myTable2.Rows(0)(0))
                        myComm.Parameters.AddWithValue("@2", myTable2.Rows(0)(1))
                        myComm.Parameters.AddWithValue("@3", myTable2.Rows(0)(2))
                    Else
                        myComm.Parameters.AddWithValue("@1", 0)
                        myComm.Parameters.AddWithValue("@2", 0)
                        myComm.Parameters.AddWithValue("@3", 0)
                    End If
                    myComm.Parameters.AddWithValue("@4", row(0))
                    myComm.Parameters.AddWithValue("@5", TuThang)
                    myComm.Parameters.AddWithValue("@6", TuNam)
                    myComm.Parameters.AddWithValue("@7", DenThang)
                    myComm.Parameters.AddWithValue("@8", DenNam)
                    myComm.ExecuteNonQuery()
                End If
                CapNhatGiaVonHangBan(row(0), DenThang, DenNam)
            Next
            strSql = "Insert into KhoaSo(Thang, Nam) values( @Thang, @Nam)"
            myComm.CommandText = strSql
            myComm.Parameters.Clear()
            myComm.Parameters.AddWithValue("@Thang", TuThang)
            myComm.Parameters.AddWithValue("@Nam", TuNam)
            myComm.ExecuteNonQuery()
        Catch ex As Exception
            myConn.Close()
            myConn.Dispose()
            Return False
        End Try
        myConn.Close()
        myConn.Dispose()
        Return True
    End Function

    Private Function checkExist(ByVal MaHH As String, ByVal Thang As Integer, ByVal Nam As Integer) As Boolean
        Dim strSql As String = "Select count(*) from CTHangHoa where MaHH = @MaHH and Thang = @Thang and Nam = @Nam"
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myComm As New SqlCommand
        Dim result As Boolean = False
        Try
            myConn.Open()
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@MaHH", MaHH)
            myComm.Parameters.AddWithValue("@Thang", Thang)
            myComm.Parameters.AddWithValue("@Nam", Nam)
            myComm.Connection = myConn
            If myComm.ExecuteScalar() > 0 Then
                result = True
            End If
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return result
    End Function

    Private Sub btnDong_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDong.Click
        Me.Close()
    End Sub
End Class